package jdbc2;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

/**
 * 2.0预编译，避免注入风险
 * 测试不能传值也不能返回值，因为没有地方返回呀
 * 预编译连接与基本链接有一些不一样，一个是没有注册驱动，然后api不一样用prepareStatement（）方法获取预编译对象，
 * 里面传sql语句，而基础链接createStatement（）里面不传，他在执行时传
 * 再就是预编译连接会多一步设置？的值
 */

public class JdbcLearn2 {
    public static void main(String[] args) throws SQLException {
        JdbcLearn2 jdbcLearn2 = new JdbcLearn2();
        ArrayList<user> users = jdbcLearn2.selectDate();
        System.out.println("管理员\t密码");
        for (int i = 0; i < users.size(); i++) {
            String admin = users.get(i).getAdmin();
            String password = users.get(i).getPassword();
            System.out.println(admin+"\t"+password+"\t");
        }
    }

    /**
     * 1.校验是否合法管理员
     */
    public void isAdmin() throws SQLException {
        String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        //接收用户的数据
        String admin="aaa";
        String pwd="12345";
        //定义sql
        String sql = "select * from admins where admin=? and password=?";
        //获取预编译好的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置？的值，第一个是索引（第几个问号），第二个是变量值
        preparedStatement.setString(1,admin);//”aaa“
        preparedStatement.setString(2,pwd);//”123456“
        //执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            System.out.println("登陆成功");
        }else{
            System.out.println("登陆失败");
        }
    }

    /**
     * 2.插入数据
     * @throws SQLException
     */
    @Test
    public void insertDate() throws SQLException {
        Connection conn = getConn();
        String sql = "insert into admins values(?,?)";
        String admin="bbb";
        String pwd="12345";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,admin);
        preparedStatement.setString(2,pwd);
        int i = preparedStatement.executeUpdate();
//        return i;
    }

    /**
     * 3.删除数据
     * @throws SQLException
     */
    @Test
    public void deleteDate() throws SQLException {
        String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "delete from admins where admin=?";
        String admin="aaa";
//        String pwd="12345";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,admin);
//        preparedStatement.setString(2,pwd);
        int i = preparedStatement.executeUpdate();
        System.out.println("i = " + i);
//        return i;
    }

    /**
     * 4.更新数据
     * @throws SQLException
     */
    @Test
    public void updateDate() throws SQLException {
        String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "update admins set password=? where admin=?";
        String admin="bbb";
        String pwd="66666";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,pwd);
        preparedStatement.setString(2,admin);
        int i = preparedStatement.executeUpdate();
        System.out.println("i = " + i);
//        return i;
    }

    /**
     * 5.查询数据，创建实体类，将值放到对象里面，再把对象装进集合
     * @return
     * @throws SQLException
     */
    public ArrayList<user> selectDate() throws SQLException {
        ArrayList<user> users = new ArrayList<>();
        String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "select * from admins";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            String admin = resultSet.getString("admin");
            String password1 = resultSet.getString("password");
            user user = new user();
            user.setAdmin(admin);
            user.setPassword(password1);
            users.add(user);
        }
        return users;
    }
    public Connection getConn() throws SQLException {
        String url="jdbc:mysql://localhost:3306/practice?useSSL=false&serverTimezone=UTC";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }
}
